import '../database/database.dart';
import '../database/entities/diary.dart';

class DiaryServices {
  DiaryServices();

  /// 创建日记
  static Future<DiaryEntity?> createDiary(DiaryEntity diary) async {
    return DB.instance.insert(DiaryTable.tableName, diary);
  }

  /// 删除日记
  static Future<bool> deleteDiary(int id) async {
    return DB.instance.delete(DiaryTable.tableName, id);
  }

  /// 更新日记
  static Future<DiaryEntity?> updateDiary(DiaryEntity diary) async {
    return DB.instance.update(DiaryTable.tableName, diary);
  }

  /// 根据 SQL 获取日记列表
  static Future<List<DiaryEntity>?> _getDiaryList(String sql) async {
    bool opened = await DB.instance.openDB();
    if (!opened) return null;

    List<Map<String, Object?>> list = await DB.instance.db!.rawQuery(sql);
    return list.map((source) => DiaryEntity().fromMap(source)).toList();
  }

  /// 根据日期倒序加载所有日记列表
  static Future<List<DiaryEntity>?> queryAll() async {
    String tableName = DiaryTable.tableName;
    String sql = 'SELECT * FROM $tableName order by ${DiaryTable.date} desc';
    return _getDiaryList(sql);
  }

  /// 加载特定日期历史记录（按时间倒序）
  static Future<List<DiaryEntity>?> queryByDate(DateTime queryDay) async {
    DateTime startDate = DateTime(queryDay.year, queryDay.month, queryDay.day);
    int startTime = startDate.millisecondsSinceEpoch;
    DateTime endDate = startDate
        .add(const Duration(days: 1))
        .subtract(const Duration(seconds: 1));
    int endTime = endDate.millisecondsSinceEpoch;
    String tableName = DiaryTable.tableName;
    String sql = '''
        SELECT * FROM $tableName
        where ${DiaryTable.date} between $startTime and $endTime
        order by ${DiaryTable.date} desc
        ''';

    return _getDiaryList(sql);
  }

  /// 加载特定月历史记录（按时间倒序）
  static Future<List<DiaryEntity>?> queryByMonth(DateTime dateTime) async {
    DateTime startDate = DateTime(dateTime.year, dateTime.month);
    int startTime = startDate.millisecondsSinceEpoch;
    DateTime endDate = DateTime(dateTime.year, dateTime.month + 1)
        .subtract(const Duration(seconds: 1));
    int endTime = endDate.millisecondsSinceEpoch;
    String sql = '''
        SELECT * FROM ${DiaryTable.tableName}
        where ${DiaryTable.date} between $startTime and $endTime
        order by ${DiaryTable.date} desc
        ''';

    return _getDiaryList(sql);
  }

  /// 根据 id 加载详情
  static Future<DiaryEntity?> queryById(int? id) async {
    if (id == null) {
      return null;
    }

    String tableName = DiaryTable.tableName;
    String sql = 'SELECT * FROM $tableName where ${DiaryTable.id}=$id';
    List<DiaryEntity>? list = await _getDiaryList(sql);
    return list == null || list.isEmpty ? null : list[0];
  }
}
